package mobi.hami.huapin.dao;


import mobi.hami.huapin.daobase.CrudMapper;
import mobi.hami.huapin.dto.UserNoteDTO;
import mobi.hami.huapin.dto.UserNoteLogDTO;
import mobi.hami.huapin.model.UserNoteLog;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;

@Repository
public interface UserNoteLogMapper extends CrudMapper<UserNoteLog> {

    /**
     * 根据空间Id获取当天被贴条的信息
     * @param spaceId
     * @return
     */
    @Select("select * from user_note_log\n" +
            "where space_id=#{spicId} and to_days(create_time) = to_days(now())\n" +
            "order by create_time desc ")
    List<UserNoteLog> getCurrentDayUserBySpaceId(Integer spaceId);

    /**
     * 根据空间Id获取昨天新增的被系统的贴条信息
     * @param spaceId
     * @return
     */
    @Select("select * from user_note_log\n" +
            "where from_user_id=0 and TO_DAYS(NOW()) - TO_DAYS(create_time) = 1")
    List<UserNoteLog> getYesterdayNewUserBySpaceId(Integer spaceId);

    /**
     * 获取当前空间下其它用户（List）
     * @param spaceId
     * @param userId
     * @return
     */
    @Select("select  to_user_id as user_id,min(myCount) as note_count from\n" +
            "  (select to_user_id,count(0) myCount from user_note_log\n" +
            "   where date(create_time)>= DATE_SUB(CURDATE(), INTERVAL 7 DAY)\n" +
            "   and space_id=#{spaceId} and to_user_id !=#{userId}\n" +
            "   group by to_user_id\n" +
            "   UNION ALL\n" +
            "   select b.to_visit_user, count(a.id) from user_note_log a\n" +
            "   RIGHT join (\n" +
            "        select to_visit_user,max(create_time) create_time from user_visit_log\n" +
            "        where date(create_time)>= DATE_SUB(CURDATE(), INTERVAL 7 DAY)\n" +
            "          and to_visit_user !=#{userId} and space_id=#{spaceId}  and from_visit_user=#{userId} and to_visit_user!=0\n" +
            "        group by to_visit_user)\n" +
            "  b on a.to_user_id=b.to_visit_user and a.create_time>=b.create_time\n" +
            "  group by b.to_visit_user) a\n" +
            "GROUP BY to_user_id\n" +
            "order by note_count desc\n" +
            "LIMIT 100;")
    List<UserNoteDTO> getUserNoteBySpaceIdAndUserId(@Param("spaceId")Integer spaceId, @Param("userId") Integer userId);

    /**
     * 获取当前用户被贴的纸条数
     * @param spaceId
     * @param userId
     * @return
     */
    @Select("select count(0) myCount from user_note_log\n" +
            "where space_id=#{spaceId} and  to_user_id=#{userId} and  create_time>=(\n" +
            "  select create_time from user_visit_log\n" +
            "  where from_visit_user=#{userId} and to_visit_user=#{userId} and space_id=#{spaceId}\n" +
            "  group by id desc\n" +
            "  limit 1)\n" +
            "group by to_user_id")
    Integer getUserNoteLogCount(@Param("spaceId")Integer spaceId, @Param("userId") Integer userId);


    /**
     * 获取当前用户当前时间贴出的纸条被回复的个数
     * @param spaceId
     * @param userId
     * @return
     */
    @Select("select count(a.id)myCount from user_note_log a\n" +
            "        join  ( select to_visit_user ,\n" +
            "                   CASE when  max_time> ifnull(create_time,DATE_SUB(now(),interval 1 year))\n" +
            "                        then max_time ELSE  create_time END create_time\n" +
            "                   FROM (\n" +
            "                          select to_visit_user,max(create_time) max_time,\n" +
            "                                  (select create_time  from user_visit_log\n" +
            "                                    where from_visit_user=#{userId} and space_id=#{spaceId} and to_visit_user=0\n" +
            "                                    order by id desc LIMIT  1) create_time\n" +
            "                          from user_visit_log\n" +
            "                          where from_visit_user=#{userId} and space_id=#{spaceId}\n" +
            "                          group by to_visit_user\n" +
            "                        ) a\n" +
            "              ) b\n" +
            "      on a.to_user_id=b.to_visit_user and a.reply_time>b.create_time\n" +
            "      where a.from_user_id=#{userId} and is_reply in (2,4) and a.space_id=#{spaceId}")
    Integer getUserReplyCount(@Param("spaceId")Integer spaceId, @Param("userId") Integer userId);

    /**
     * 获取7天被贴的纸条
     * @param spaceId
     * @param userId
     * @return
     */
    @Select("select * from user_note_log\n" +
            "  where space_id=#{spaceId} and to_user_id=#{userId}\n" +
            "        and date(create_time)>= DATE_SUB(CURDATE(), INTERVAL 7 DAY);")
    List<UserNoteLog> getUserByNote(@Param("spaceId")Integer spaceId, @Param("userId") Integer userId);


    /**
     * 批量插入
     * @param userNoteLogList
     * @return
     */
    @Insert({"<script> Insert INTO user_note_log(space_id,to_user_id,from_user_id,note_id,note_comtent,create_time,is_reply) Values" +
            "<foreach item='item' collection='list' separator=\",\"> " +
            "(#{item.space_id}," +
            " #{item.to_user_id}," +
            " #{item.from_user_id}," +
            " #{item.note_id}," +
            " #{item.note_comtent}," +
            " #{item.create_time}," +
            " #{item.is_reply}" +
            ")</foreach></script>"})
    @Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
    int insertListDTO(List<UserNoteLogDTO> userNoteLogList);

    @Select("select * from user_note_log where to_user_id=#{userId} and space_id=#{spaceId} order by Id desc ")
    List<UserNoteLogDTO> findListByToUserId(@Param("spaceId")Integer spaceId,@Param("userId") Integer userId);

    @Select("select a.*, (case when b.note_long_id>0 then 1 else 0 end) as IsThumbup from user_note_log a\n" +
            "left join (select note_long_id from thumbup_log where create_user_id=#{currentUserId}\n" +
            "group by note_long_id) b on a.id=b.note_long_id\n" +
            "where a.to_user_id=#{searchUserId} and a.space_id=#{spaceId}\n" +
            "order by a.id desc ")
    List<UserNoteLogDTO> findListThumbupByUserId(@Param("spaceId")Integer spaceId,
                                                 @Param("searchUserId") Integer searchUserId,
                                                 @Param("currentUserId")Integer currentUserId);


    /**
     *  获取用户被帖回复数
     * @param userId
     * @return
     */
    @Select(" select  count(*) from user_note_log where to_user_id = #{userId} and is_reply > 1 ")
    int getUserAllReplyCount(@Param("userId") Integer userId);

    /**
     * 根据查询条件获取最新的被贴信息
     * @param userId
     * @param spaceId
     * @return
     */
    @Select("select * from user_note_log where to_user_id=#{userId} and space_id=#{spaceId}\n" +
            "order by id desc LIMIT 1;")
    UserNoteLogDTO findModelByModel(@Param("userId") Integer userId,@Param("spaceId")Integer spaceId);

    /**
     * 更新用户回复的状态
     * @param id
     * @param replyStatus
     * @return
     */
    @Update("update user_note_log set is_reply=#{replyStatus},reply_time=Now() WHERE id=#{id}")
    Integer updateUserNoteLogReply(@Param("id")Integer id,@Param("replyStatus")Integer replyStatus);

    /**
     * 获取被贴出的纸条,回复列表
     * @param spaceId
     * @param userId
     * @return
     */
    @Select("select a.*,(case b.id when b.id>0 then 1 else 0 end) as IsThumbup,c.head_photo as headImage\n" +
            "from user_note_log a\n" +
            "left join thumbup_log b  on a.id=b.note_long_id and b.create_user_id=#{userId}\n" +
            "left join user_info c on a.to_user_id=c.id\n" +
            "where from_user_id=#{userId} and space_id=#{spaceId}\n" +
            "order by reply_time desc")
    List<UserNoteLogDTO> getReplyUserNoteLogList(@Param("spaceId")Integer spaceId,@Param("userId")Integer userId);

    /**
     * 获取当前被贴纸条的个数
     * @param spaceId
     * @param userId
     * @return
     */
    @Select("select count(0) from user_note_log\n" +
            "where to_user_id=#{userId} and space_id=#{spaceId}\n" +
            "      and to_days(create_time) = to_days(now())")
    Integer getCurrentDayNoteLogSum(@Param("spaceId")Integer spaceId,@Param("userId")Integer userId);

    /**
     *  查询当天，给别人贴条数
     * @param to_user_id
     * @param from_user_id
     * @return
     */
    @Select(" select  count(*) from user_note_log where to_user_id = #{to_user_id} and from_user_id = #{from_user_id} and create_time >= curdate()  ")
    int queryCurDayGiveCount(@Param("userId") Integer to_user_id,@Param("userId") Integer from_user_id);

    /**
     *  查询最近3天的用户帖同一个人的记录
     * @param to_user_id
     * @param from_user_id
     * @return
     */
    @Select(" select  date(create_time) as create_time ,count(*) as notecount from user_note_log where create_time > DATE_SUB(CURDATE(),INTERVAL 3 DAY) and to_user_id = #{to_user_id} and from_user_id =#{from_user_id} group by  date(create_time) order by  date(create_time) asc  ")
    List<UserNoteLog> queryUserGiveList(@Param("to_user_id") Integer to_user_id,@Param("from_user_id") Integer from_user_id);

    /**
     * 增加点赞次数
     * @param Id
     * @return
     */
    @Select("update user_note_log set thumbup_counts=ifnull(thumbup_counts,0)+1 where id=#{Id}")
    Integer addThubupCountById(Integer Id);


    /**
     * 获取该空间下最近7天内的用户的气泡数前5的信息
     * @param spaceId
     * @return
     */
    @Select("select a.*,b.head_photo as user_head from (\n" +
            "select to_user_id as user_id , count(0) note_count from user_note_log\n" +
            "   where space_id=#{spaceId}  and date(create_time)>= DATE_SUB(CURDATE(), INTERVAL 7 DAY)\n" +
            "   group by to_user_id\n" +
            "   order by note_count desc\n" +
            "   LIMIT 5) a\n" +
            "left join user_info b on a.user_id=b.id;")
    List<UserNoteDTO> getUserNoteLogSevenDayCountBySpaceId(Integer spaceId);

    /**
     * 获取该空间下最新的被贴的纸条信息
     * @param spaceId
     * @return
     */
    @Select("select note_id,note_comtent,MAX(create_time) create_time from user_note_log where space_id=#{spaceId}\n" +
            "  group by note_id,note_comtent\n" +
            "order by create_time desc\n" +
            "LIMIT 4")
     List<Map> getLastNewFiveNoteLogBySpaceId(Integer spaceId);

}